package ru.javadevelop.vegetarian.common;

import java.sql.*;

/**
 * http://t-menu.ru
 * User: navff
 * Date: 24.07.12
 * Time: 16:58
 */
public enum DB
{
    INSTANCE;

    private Connection conn;

    public int getLastId()
    {
        return executeSql(new Handler<Integer>() {
            @Override
            public Integer execute(Statement stmt) throws SQLException {
                ResultSet rset = stmt.getResultSet();
                rset.next();

                return rset.getInt(1);
            }
        }, "SELECT last_insert_rowid()");
    }

    public int executeSql(String sql, Object... params)
    {
        return executeSql(new Handler<Integer>() {
            @Override
            public Integer execute(Statement stmt) throws SQLException {
                return stmt.getUpdateCount();
            }
        }, sql, params);
    }

    public <T> T executeSql(Handler<T> action, String sql, Object... params)
    {
        PreparedStatement stmt = null;
        try
        {
            stmt = conn.prepareStatement(sql);
            for (int idx=0; idx<params.length; idx++)
                stmt.setObject(idx + 1, params[idx]);

            stmt.execute();

            return action.execute(stmt);
        }
        catch (SQLException e)
        {
            throw new RuntimeException("Error executing SQL: " + sql, e);
        }
        finally
        {
            if (stmt != null)
            {
                try
                {
                    stmt.close();
                }
                catch (SQLException e)
                {
                    throw new RuntimeException("Failed to close SQL statement", e);
                }
            }
        }
    }

    public void openConnection(String jdbcUrl, String username, String password)
    {
        try
        {
//            Class.forName(SQLDroidDriver.class.getName());
            Class.forName(org.h2.Driver.class.getName());
            this.conn = DriverManager.getConnection(jdbcUrl, username, password);
            this.checkOrCreate();

        }
        catch (ClassNotFoundException e)
        {
            throw new RuntimeException(e);
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
    }

    public void closeConnection()
    {
    }

    public static interface Handler<T>
    {
        T execute(Statement stmt) throws SQLException;
    }

    private void checkOrCreate()
    {
        String CHECKING_TABLE = "product";
        String checkQuery = "SELECT * FROM "+ CHECKING_TABLE + " LIMIT 1;";
        try
        {
            executeSql(checkQuery);
        }
        catch (RuntimeException ex)
        {
            //  Берем из файла скрипт для создания таблиц БД
            String dropQuery = FileUtils.readRawTextFile("/delete_data.sql");
            executeSql(dropQuery);
            String createQuery = FileUtils.readRawTextFile("/create_tables.sql");
            executeSql(createQuery);
            String fillQuery = FileUtils.readRawTextFile("/demo_data.sql");
            executeSql(fillQuery);
        }
    }
}
